import os
import sqlite3
# This needs to be imported explicitly so it's caught by cxfreeze
import sqlite3.dump
import re

from enum import AEnums
import DriveUtil
from mixins import pathMixin
from contextlib import contextmanager
import logging
import zipfile

logger = logging.getLogger('AnyBackup')


class AbortTransaction(Exception):
    pass


# noinspection PyUnresolvedReferences
def getHomedir():
    try:
        from win32com.shell import shellcon, shell
        homedir = shell.SHGetFolderPath(0, shellcon.CSIDL_APPDATA, 0, 0)
    except ImportError:  # quick semi-nasty fallback for non-windows/win32com case
        homedir = os.path.expanduser("~")
    homedir += '/AnyBackup'
    return homedir


def buildAnyBackupDB(sql):
    logger.info('Doing initial build of AnyBackup tables...')
    c = sql.cursor()
    c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(
        AEnums.VALIDEXTS, AEnums.EXT, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(
        AEnums.SKIPLIST, AEnums.SKIP, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} real, {5} real,
                {6} text, {7} text, {8} text, {9} text, {10} integer, {11} text,
                PRIMARY KEY ({1}))'''.format(
        AEnums.FILES, AEnums.ID, AEnums.NAME, AEnums.PATH,
        AEnums.MTIME, AEnums.SIZE, AEnums.SERIAL,
        AEnums.VOLNAME, AEnums.PARENT, AEnums.PREFIX, AEnums.DIR,
        AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
                {4} real, {5} real, {6} text, {7} text, {8} text, {9} text,
                PRIMARY KEY ({1}, {2}, {6}))'''.format(AEnums.DRIVES, AEnums.NAME,
                                                       AEnums.SERIAL, AEnums.TYPE, AEnums.FREESPACE,
                                                       AEnums.TOTALSPACE,
                                                       AEnums.SETNAME, AEnums.REMOTEHOST,
                                                       AEnums.REMOTEPATH, AEnums.CHKDSK))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
              PRIMARY KEY ({1},{2},{3}))'''.format(
        AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
                PRIMARY KEY ({1},{2},{3},{4}))'''.format(
        AEnums.PATHS, AEnums.PATH, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME
    ))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
            PRIMARY KEY ({1}))'''.format(
        AEnums.ROOT, AEnums.ID, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME,
    ))
    c.execute('''CREATE TABLE {0} ({1} text, PRIMARY KEY ({1}))'''.format(
        AEnums.SETS, AEnums.SETNAME
    ))
    c.execute('''INSERT INTO {0} VALUES (?)'''.format(
        AEnums.SETS), ['AnyBackup']
    )
    sql.commit()


class SQLLite(pathMixin):
    sql = None

    # noinspection PyArgumentList
    def __init__(self, guest=None):
        self.transaction = False
        self.homedir = getHomedir()
        if guest:
            try:
                n = zipfile.ZipFile(guest, 'r')
                path = '/temp/%s' % os.path.basename(guest).replace('.zip', '')
                n.extractall('/temp')
                self.sql = sqlite3.connect(path, check_same_thread=False)
            except zipfile.BadZipfile:
                self.sql = sqlite3.connect(guest, check_same_thread=False)
        elif not self.sql:
            init = not os.path.exists('%s/anybackup.db' % self.homedir)
            self.sql = sqlite3.connect('%s/anybackup.db' % self.homedir, check_same_thread=False)
            if init:
                buildAnyBackupDB(self.sql)
        self.initialize()

    def initialize(self):
        c = self.sql.cursor()
        logger.info('Verifying db indices...')
        c.execute('''PRAGMA INDEX_LIST('%s')''' % AEnums.FILES)
        results = c.fetchall()
        if 'parent_index' not in [x[1] for x in results]:
            logger.info('Creating parent index.')
            c.execute('''CREATE INDEX parent_index on {table} ({parent}, {dir})'''.format(
                table=AEnums.FILES, dir=AEnums.DIR, parent=AEnums.PARENT
            ))
        if 'drive_index' not in [x[1] for x in results]:
            logger.info('Creating drive index.')
            c.execute('''CREATE INDEX drive_index on {table} ({vol}, {serial}, {set})'''.format(
                table=AEnums.FILES, vol=AEnums.VOLNAME, serial=AEnums.SERIAL, set=AEnums.SETNAME
            ))
        # noinspection PyBroadException
        try:
            c.execute('SELECT {0} FROM {1}'.format(AEnums.CHKDSK, AEnums.DRIVES))
        except:
            logger.info('Chkdsk column not present, adding it.')
            c.execute('ALTER TABLE {0} ADD COLUMN {1} text'.format(AEnums.DRIVES, AEnums.CHKDSK))
        logger.info('Setting pragmas...')
        c.execute('''PRAGMA synchronous=OFF''')
        c.execute('''PRAGMA count_changes=OFF''')
        c.execute("PRAGMA page_size = 4096")
        c.execute("PRAGMA cache_size = 16384")
        c.execute("PRAGMA temp_store = MEMORY")
        self.sql.commit()

    def commit(self):
        self.sql.commit()

    def rollback(self):
        self.sql.rollback()

    @contextmanager
    def batch(self):
        commit = False
        try:
            logger.info('Starting transaction')
            self.transaction = True
            yield
            self.commit()
            commit = True
            logger.info('Transaction finished')
        except AbortTransaction:
            commit = True
            logger.info('Transaction Aborted')
            self.rollback()
        finally:
            if not commit:
                logger.info('Transaction not committed.')
            self.rollback()
            self.transaction = False

    def addValidExt(self, ext, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [ext, backupSet])
        if not self.transaction:
            self.sql.commit()

    def setValidExt(self, ext, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [backupSet]
        )
        if type(ext) != str:
            lst = ext
        else:
            lst = [ext]
        for ext in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.VALIDEXTS),
                      [ext, backupSet])
        if not self.transaction:
            self.sql.commit()

    def addSkip(self, skip, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST), [skip, backupSet])
        if not self.transaction:
            self.sql.commit()

    def setSkip(self, skip, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST, AEnums.SETNAME), [backupSet]
        )
        if type(skip) != str:
            lst = skip
        else:
            lst = [skip]
        for skip in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST),
                      [skip, backupSet])
        if not self.transaction:
            self.sql.commit()

    def getSets(self):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1}'''.format(
            AEnums.SETNAME, AEnums.SETS,
        ))
        return [x[0] for x in c.fetchall()]

    def addSet(self, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?)'''.format(
            AEnums.SETS), [backupSet]
        )
        if not self.transaction:
            self.sql.commit()

    def removeSet(self, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SETS, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.FILES, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.DRIVES, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.LOCKED, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.ROOT, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.PATHS, AEnums.SETNAME), [backupSet],
        )
        if not self.transaction:
            self.sql.commit()

    def getValidExt(self, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.EXT, AEnums.VALIDEXTS, AEnums.SETNAME),
            [backupSet],
        )
        return [x[0] for x in c.fetchall()]

    def getSkipList(self, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.SKIP, AEnums.SKIPLIST, AEnums.SETNAME),
            [backupSet],
        )
        return [x[0] for x in c.fetchall()]

    def getDrive(self, name, serial, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME), [name, serial, backupSet])
        ret = c.fetchone()
        if ret:
            return {
                AEnums.NAME: ret[0],
                AEnums.SERIAL: ret[1],
                AEnums.TYPE: ret[2],
                AEnums.FREESPACE: ret[3],
                AEnums.TOTALSPACE: ret[4],
                AEnums.SETNAME: ret[5],
                AEnums.REMOTEHOST: ret[6],
                AEnums.REMOTEPATH: ret[7],
                AEnums.CHKDSK: ret[8],
            }
        return None

    def getDriveByFile(self, fileRef):
        return self.getDrive(fileRef[AEnums.VOLNAME],
                             fileRef[AEnums.SERIAL],
                             fileRef[AEnums.SETNAME])

    def updateDrive(self, dd):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME]],
        )
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.TYPE],
                dd[AEnums.FREESPACE], dd[AEnums.TOTALSPACE], dd[AEnums.SETNAME],
                dd.get(AEnums.REMOTEHOST, ''), dd.get(AEnums.REMOTEPATH, ''),
                dd.get(AEnums.CHKDSK, '')]
        )
        if not self.transaction:
            self.sql.commit()

    def removeFiles(self, drives):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
                ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
                ])
        if not self.transaction:
            self.sql.commit()

    def updateFreeSpace(self, d):
        letter = DriveUtil.getLetter(d[AEnums.NAME], d[AEnums.SERIAL])
        if not letter:
            return False
        freeSpace = DriveUtil.getFreeSpace(letter)
        totalSpace = DriveUtil.getTotalSpace(letter)
        d[AEnums.FREESPACE] = freeSpace
        d[AEnums.TOTALSPACE] = totalSpace
        return self.updateDrive(d)

    def getDriveFiles(self, d):
        return self.getFilesByDrive(d, fType=AEnums.ALL)

    def isLocked(self, drive):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        return bool(c.fetchall())

    def findDir(self, drive, path):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
            AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.PATH),
            [
                drive[AEnums.NAME], drive[AEnums.SETNAME], drive[AEnums.SERIAL], path + '%',
            ])
        return bool(c.fetchall())

    def getFile(self, fildId):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=?'''.format(
            AEnums.FILES, AEnums.ID), [fildId]
        )
        results = c.fetchone()
        if results:
            return {
                AEnums.ID: results[0],
                AEnums.NAME: results[1],
                AEnums.PATH: results[2],
                AEnums.MTIME: results[3],
                AEnums.SIZE: results[4],
                AEnums.SERIAL: results[5],
                AEnums.VOLNAME: results[6],
                AEnums.PARENT: results[7],
                AEnums.PREFIX: results[8],
                AEnums.DIR: bool(results[9]),
                AEnums.SETNAME: results[10],
            }

    def getFullPaths(self, files, driveType=AEnums.CONTENT):
        letters = {}
        paths = []
        for fileRef in files:
            k = (fileRef[AEnums.VOLNAME], fileRef[AEnums.SERIAL])
            if not k in letters:
                letters[k] = DriveUtil.getLetter(*k)
            if not letters[k]:
                paths.append(None)
            else:
                if driveType == AEnums.CONTENT:
                    paths.append(self.createPath(letters[k] + ':', fileRef[AEnums.PATH], fileRef[AEnums.NAME]))
                else:
                    paths.append(self.createPath(letters[k] + ':', fileRef[AEnums.SETNAME], fileRef[AEnums.PATH],
                                                 fileRef[AEnums.NAME]))
        return paths

    def getFullPath(self, fileRef):
        drive = self.getDriveByFile(fileRef)
        paths = self.getFullPaths([fileRef], drive[AEnums.TYPE])
        return paths[0]

    def getPaths(self, drive):
        c = self.sql.cursor()
        c.execute('''SELECT {0} from {1} WHERE {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATH, AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        ret = c.fetchall()
        return [x[0] for x in ret]

    def addPath(self, drive, path):
        returnCode = True
        letter = DriveUtil.getLetter(drive[AEnums.NAME], drive[AEnums.SERIAL])
        newPath = re.sub(r'^%s:' % letter, '', path)
        if not re.search(r'%s$' % re.escape(os.sep), newPath, re.IGNORECASE):
            newPath += os.sep
        result = self.containsPath(drive, newPath)
        if result == AEnums.OVERWRITE:
            self.containsPath(drive, newPath, overwrite=True)
            returnCode = AEnums.OVERWRITE
        elif result:
            return False
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
            AEnums.PATHS),
            [newPath, drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]]
        )
        if not self.transaction:
            self.sql.commit()
        return returnCode

    def containsPath(self, drive, path, overwrite=False):
        for directory in self.getPaths(drive):
            oldPath = directory
            if self._containsPath(oldPath, path):
                return True
            elif self._containsPath(path, oldPath):
                if overwrite:
                    self.removePath(drive, oldPath)
                return AEnums.OVERWRITE
        return False

    def _containsPath(self, dirName, subdir):
        if dirName.endswith(os.sep):
            dirName = dirName[0:-1]
        if subdir.endswith(os.sep):
            subdir = subdir[0:-1]
        head, tail = os.path.split(subdir)
        while head and tail:
            if head == dirName:
                return True
            head, tail = os.path.split(head)
        else:
            return False

    def removePath(self, drive, path):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.PATH, AEnums.SETNAME),
            [drive[AEnums.NAME], drive[AEnums.SERIAL], path, drive[AEnums.SETNAME]],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
            AEnums.FILES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.PATH),
            [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME], path + '%'],
        )

    def setRoot(self, drive, rootFiles):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
            ])
        for fileRef in rootFiles:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT), [fileRef, drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]]
            )
        if not self.transaction:
            self.sql.commit()

    def getFiles(self, name, dirs=False):
        c = self.sql.cursor()
        if dirs:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.FILES, AEnums.PARENT, AEnums.DIR), [name, 1]
            )
        else:
            c.execute('''SELECT * FROM {0} WHERE {1}=?'''.format(
                AEnums.FILES, AEnums.PARENT), [name]
            )

        ret = c.fetchall()
        ret = self.convertFiles(ret)
        return ret

    def getUsageByDrive(self, drive):
        c = self.sql.cursor()
        c.execute('''SELECT sum({0}) FROM {1} WHERE {2}=? AND {3}=? AND {4}=?'''.format(
                  AEnums.SIZE, AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
                  [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]])
        ret = c.fetchall()
        if ret:
            return ret[0][0]

    def getFilesByDrive(self, drive, fType=AEnums.FILES):
        c = self.sql.cursor()
        if fType == AEnums.FILES:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=?'''.format(
                AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.DIR),
                [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME], 0]
            )
        elif fType == AEnums.DIR:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=?'''.format(
                AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.DIR),
                [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME], 1]
            )
        else:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
                [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]]
            )
        return self.convertFiles(c.fetchall())

    def getRootFile(self, drive, dirs=False):
        c = self.sql.cursor()
        if dirs:
            c.execute('''SELECT * FROM {0} WHERE {1}=? {2} in (SELECT {2} FROM {3} WHERE {4}=? AND {5}=?)'''.format(
                AEnums.FILES, AEnums.ID, AEnums.DIR, AEnums.ROOT, AEnums.NAME, AEnums.SERIAL),
                [1, drive[AEnums.NAME], drive[AEnums.SERIAL]],
            )
        else:
            c.execute('''SELECT * FROM {0} WHERE {1} in (SELECT {1} FROM {2} WHERE {3}=? AND {4}=?)'''.format(
                AEnums.FILES, AEnums.ID, AEnums.ROOT, AEnums.NAME, AEnums.SERIAL),
                [drive[AEnums.NAME], drive[AEnums.SERIAL]],
            )
        return self.convertFiles(c.fetchall())

    def addDrive(self, dd, paths=None):
        c = self.sql.cursor()
        if AEnums.FREESPACE not in dd:
            dd[AEnums.FREESPACE] = 1
        if AEnums.TOTALSPACE not in dd:
            dd[AEnums.TOTALSPACE] = 1
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME],
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? and {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME],
            ])
        if not paths:
            paths = [os.sep]
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.TYPE],
                dd[AEnums.FREESPACE], dd[AEnums.TOTALSPACE], dd[AEnums.SETNAME],
                dd.get(AEnums.REMOTEHOST, ''), dd.get(AEnums.REMOTEPATH, ''),
                dd.get(AEnums.CHKDSK, ''),
            ])
        for path in paths:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.PATHS),
                [
                    path, dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME]
                ])
        if not self.transaction:
            return self.sql.commit()

    def addFile(self, drive, fd, root=False):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?,?,?,?)'''.format(
            AEnums.FILES),
            [
                fd[AEnums.ID], fd[AEnums.NAME], fd[AEnums.PATH],
                fd[AEnums.MTIME], fd[AEnums.SIZE], fd[AEnums.SERIAL],
                fd[AEnums.VOLNAME], fd[AEnums.PARENT],
                fd[AEnums.PREFIX], int(fd[AEnums.DIR]), fd[AEnums.SETNAME],
            ])
        if root:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT),
                [
                    fd[AEnums.ID], drive[AEnums.NAME],
                    drive[AEnums.SERIAL], fd[AEnums.SETNAME],
                ])
        if not self.transaction:
            self.sql.commit()

    def commit(self):
        self.sql.commit()

    def removeDrive(self, drive):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        if not self.transaction:
            return self.sql.commit()

    def search(self, regex, backupSet):
        c = self.sql.cursor()
        d = regex[AEnums.DRIVE]
        driveTypes = [AEnums.CONTENT, AEnums.BACKUP] if regex[AEnums.DRIVES] == AEnums.ALL else [regex[AEnums.DRIVES]]
        if d:
            if regex[AEnums.TYPE] != AEnums.ALL:
                c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=? AND {5} LIKE ?'''.format(
                    AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.DIR, AEnums.NAME),
                    [
                        d[AEnums.NAME], d[AEnums.SERIAL], backupSet, int(regex[AEnums.TYPE] == AEnums.DIR),
                        regex[AEnums.QUERY],
                    ])
            else:
                c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
                    AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.NAME),
                    [
                        d[AEnums.NAME], d[AEnums.SERIAL], backupSet, regex[AEnums.QUERY],
                    ])
        elif regex[AEnums.TYPE] != AEnums.ALL:
            c.execute('''SELECT * FROM {table1} JOIN {table2} ON {table1}.{volname} = {table2}.{name}
                AND {table1}.{serial} = {table2}.{serial} AND {table1}.{set} = {table2}.{set}
                WHERE {table1}.{set}=? AND {table1}.{isdir}=? AND {table1}.{name} LIKE ?
                AND {type} IN ({types})'''.format(
                table2=AEnums.DRIVES, table1=AEnums.FILES, set=AEnums.SETNAME, isdir=AEnums.DIR, name=AEnums.NAME,
                volname=AEnums.VOLNAME, serial=AEnums.SERIAL,
                type=AEnums.TYPE, types=','.join(["'%s'" % x for x in driveTypes])),
                [
                    backupSet, int(regex[AEnums.TYPE] == AEnums.DIR), regex[AEnums.QUERY],
                ])
        else:
            c.execute('''SELECT * FROM {table1} JOIN {table2} ON {table1}.{volname} = {table2}.{name}
                AND {table1}.{serial} = {table2}.{serial} AND {table1}.{set} = {table2}.{set}
                WHERE {table1}.{set}=? AND {table1}.{name} LIKE ?
                AND {type} IN ({types})'''.format(
                table2=AEnums.DRIVES, table1=AEnums.FILES, set=AEnums.SETNAME, isdir=AEnums.DIR, name=AEnums.NAME,
                volname=AEnums.VOLNAME, serial=AEnums.SERIAL,
                type=AEnums.TYPE, types=','.join(["'%s'" % x for x in driveTypes])),
                [
                    backupSet, regex[AEnums.QUERY],
                ])
        ret = c.fetchall()
        return self.convertFiles(ret)

    def convertFiles(self, ret):
        files = []
        for fileRef in ret:
            files.append({
                AEnums.ID: fileRef[0],
                AEnums.NAME: fileRef[1],
                AEnums.PATH: fileRef[2],
                AEnums.MTIME: fileRef[3],
                AEnums.SIZE: fileRef[4],
                AEnums.SERIAL: fileRef[5],
                AEnums.VOLNAME: fileRef[6],
                AEnums.PARENT: fileRef[7],
                AEnums.PREFIX: fileRef[8],
                AEnums.DIR: bool(fileRef[9]),
                AEnums.SETNAME: fileRef[10],
            })
        return files

    def removeFile(self, name, backupSet):
        files = self.getFiles(name, backupSet)
        c = self.sql.cursor()
        for fileRef in files:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.FILES, AEnums.ID, AEnums.SETNAME),
                [
                    fileRef[AEnums.ID], fileRef[AEnums.SETNAME],
                ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.ROOT, AEnums.ID, AEnums.SETNAME),
                [
                    fileRef[AEnums.ID], fileRef[AEnums.SETNAME],
                ])
        if not self.transaction:
            self.sql.commit()

    def lockDrives(self, drives, isLock):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
                ])
            if isLock:
                c.execute('''INSERT INTO {0} VALUES (?,?,?)'''.format(
                    AEnums.LOCKED),
                    [
                        drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
                    ])
        if not self.transaction:
            self.sql.commit()

    def getDrives(self, driveType, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
            AEnums.DRIVES, AEnums.TYPE, AEnums.SETNAME),
            [driveType, backupSet])
        ret = c.fetchall()
        drives = []
        for drive in ret:
            drives.append({
                AEnums.NAME: drive[0],
                AEnums.SERIAL: drive[1],
                AEnums.TYPE: drive[2],
                AEnums.FREESPACE: drive[3],
                AEnums.TOTALSPACE: drive[4],
                AEnums.SETNAME: drive[5],
                AEnums.REMOTEHOST: drive[6],
                AEnums.REMOTEPATH: drive[7],
                AEnums.CHKDSK: drive[8] or '',
            })
        return drives

    def export(self, drives, path, setname):
        if os.path.exists(path):
            os.unlink(path)
        expdb = sqlite3.connect(path)
        expdb.executescript(''.join(unicode(line) for line in self.sql.iterdump()))
        c = expdb.cursor()
        c.execute('DELETE FROM {0} WHERE {1}!=?'.format(
            AEnums.SETS, AEnums.SETNAME), [setname]
        )
        c.execute('DELETE FROM {0}'.format(AEnums.LOCKED))
        for dType in [AEnums.BACKUP, AEnums.CONTENT]:
            for d in self.getDrives(dType, setname):
                if d not in drives:
                    c.execute('DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'.format(
                        AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                        [d[AEnums.NAME], d[AEnums.SERIAL], d[AEnums.SETNAME]]
                    )
                    c.execute('DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'.format(
                        AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
                        [d[AEnums.NAME], d[AEnums.SERIAL], d[AEnums.SETNAME]]
                    )
                    c.execute('DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'.format(
                        AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                        [d[AEnums.NAME], d[AEnums.SERIAL], d[AEnums.SETNAME]]
                    )
                    c.execute('DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'.format(
                        AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                        [d[AEnums.NAME], d[AEnums.SERIAL], d[AEnums.SETNAME]]
                    )
        expdb.commit()
        expdb.close()
        with zipfile.ZipFile(path+'.zip', 'w', zipfile.ZIP_DEFLATED) as datzip:
            datzip.write(path, arcname=os.path.basename(path))
        os.unlink(path)